package com.wondersgroup.fjzlpt.org.dao.impl.jgqy.qylb;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.quick.framework.boot.repository.sql.bean.SQLBean;
import com.quick.framework.boot.repository.sql.bean.page.PageQuerySQLBean;
import com.quick.framework.boot.repository.sql.bean.table.TableBean;
import com.quick.framework.boot.repository.sql.jdbcTemplate.impl.BaseJTDAOImpl;
import com.quick.framework.boot.repository.sql.jdbcTemplate.rs.ResultHandlerConstant;
import com.wondersgroup.fjzlpt.org.dao.inter.jgqy.qylb.QylbDAO;
@Repository
public class QylbDAOImpl extends BaseJTDAOImpl implements QylbDAO{
	@Override
	@Autowired
	@Qualifier("TB_JGQY_YLT")
	public void setTb(TableBean tb) {
		super.setTb(tb);
	}
	
	@Autowired
	@Qualifier("TB_JGQY_YLT_YLJG")
	private TableBean glTb;

	/**
	 * 分页查询
	 */
	public JSONObject queryPage(Map<String, String> map) {
		JSONObject sqlObj = this.getPageSql(map);
		String sql = sqlObj.getString("sql");
		List<String> list = (List)sqlObj.get("sqlParams");
		
		PageQuerySQLBean sb = super.checkAndCreatePageQuerySQLBean(map, sql, "order by a.QYSJ DESC", list.toArray());
		return super.queryPage(sb, ResultHandlerConstant.JSONARRAY);
	}
	
	/**
	 * 根据条件查询所有数据
	 * @param map
	 * @return
	 */
	public JSONArray queryAll(Map<String, String> map){
		JSONObject sqlObj = this.getPageSql(map);
		String sql = sqlObj.getString("sql") + " order by a.QYSJ DESC";
		List<String> list = (List)sqlObj.get("sqlParams");
		
		SQLBean bean = new SQLBean(sql, list.toArray());
		return super.queryList(bean);
	}
	
	/**
	 * 生成分页sql
	 * @param map
	 * @return
	 */
	private JSONObject getPageSql(Map<String, String> map){
		JSONObject result = new JSONObject();
		
		StringBuffer sbuf = new StringBuffer("select a.*,c.YLJGMC ZDYLJGMC, ");
		sbuf.append("b.YLTMC,b.YLTLX,b.BZSM,b.CJSJ,b.ZDYLJGID,b.YLTZT,b.SFXY,");
		sbuf.append("(select YLJGMC from TB_ZD_YLJGXX where PTJGID = a.YLJGID) YLJGMC,");
		sbuf.append("(select XMMC from TB_ZY_YXQ where XMDM = a.QYYXQ) QYYXQMC,");
		sbuf.append("(select XMMC from TB_ZY_JYYY where XMDM = a.JYYY) JYYYMC,");
		sbuf.append("(select XMMC from TB_ZY_JGQY_QYZT where XMDM = a.QYZT) QYZTMC,");
		sbuf.append("(select XMMC from TB_ZY_YLTLX where XMDM = b.YLTLX) YLTLXMC \n");
		sbuf.append(" from "+this.glTb.getTableName()+" a, "+this.tb.getTableName()+" b,TB_ZD_YLJGXX c ");
		sbuf.append(" where a.YLTID = b.YLTID and b.ZDYLJGID = c.PTJGID and b.ZDYLJGID <> a.YLJGID ");
		List list = new ArrayList();
		String value = map.get("ZDYLJGMC");
		if(!StringUtils.isEmpty(value))
		{
			sbuf.append("and YLJGMC like ? ");
			list.add("%"+value+"%");
		}
		value = map.get("JLZT");
		if(!StringUtils.isEmpty(value))
		{
			sbuf.append("and a.JLZT = ? ");
			list.add(value);
		}else{
			sbuf.append("and a.JLZT = '1' ");
		}
		value = map.get("YLTID");
		if(!StringUtils.isEmpty(value))
		{
			sbuf.append("and a.YLTID = ? ");
			list.add(value);
		}
		value = map.get("YLTLX");
		if(!StringUtils.isEmpty(value))
		{
			sbuf.append("and b.YLTLX = ? ");
			list.add(value);
		}
		value = map.get("KSSJ");
		if(!StringUtils.isEmpty(value))
		{
			sbuf.append("and b.QYSJ > ? ");
			list.add(value);
		}
		value = map.get("JSSJ");
		if(!StringUtils.isEmpty(value))
		{
			sbuf.append("and b.QYSJ < ? ");
			list.add(value);
		}
		value = map.get("QYZT");
		if(!StringUtils.isEmpty(value))//3,5,6,7
		{
			sbuf.append("and a.QYZT = ? ");
			list.add(value);
		}else{
			sbuf.append("and a.QYZT in (03,05,06) ");
		}
		value = map.get("LX");
		if("qylb".equals(value)){
			sbuf.append("and a.JGLX in (1,2) ");
		}
		else if("qyjs".equals(value))//3,5,6,7
		{
			/**查询待签约记录*/
			value = map.get("YLJGID");
			if(!StringUtils.isEmpty(value))
			{
				sbuf.append("and ((b.ZDYLJGID = ? and a.JGLX = '2' ) or (a.YLJGID = ? and a.JGLX = '1'))");
				list.add(value);
				list.add(value);
			}
		}
		else if("jysh".equals(value)){
			sbuf.append("and a.JGLX = '1' ");
			value = map.get("JYFQJGID");
			if(!StringUtils.isEmpty(value))
			{
				sbuf.append("and JYFQJGID <> ? ");
				list.add(value);
			}
		}
		/**权限展示*/
		value = map.get("ZDYLJGID");
		if(!StringUtils.isEmpty(value))
		{
			sbuf.append("and (b.ZDYLJGID = ? or a.YLJGID = ? )");
			list.add(value);
			list.add(value);
		}
		
		result.put("sql", sbuf.toString());
		result.put("sqlParams", list);
		
		return result;
	}

	/**
	 * 查询签约机构
	 * @param map
	 * @return
	 */
	public JSONArray queryQyJg(Map<String, String> map) {
		StringBuffer sbuf = new StringBuffer("select a.JGLX,a.YLTID,a.QYZT,a.GLID,a.QYYXQ,a.QYSJ,a.DQSJ,b.* ");
		sbuf.append(" from "+this.glTb.getTableName()+" a, VIEW_YLJGXX b ");
		sbuf.append(" where a.YLJGID = b.PTJGID and a.QYZT in (03,07) and  a.YLTID = ?  and a.JLZT = '1' ");//可以查询不同医疗机构数据
		SQLBean sb = new SQLBean(sbuf.toString(),new String[]{map.get("YLTID")});
		return super.queryList(sb);
	}

	/**
	 * 查询医联体信息
	 * @param map
	 * @return
	 */
	public JSONObject queryYltxx(Map<String, String> map) {
		StringBuffer sbuf = new StringBuffer("select a.*,");
		sbuf.append("(select YLJGMC from TB_ZD_YLJGXX where PTJGID = a.ZDYLJGID) ZDYLJGMC,");
		sbuf.append("(select XMMC from TB_ZY_YLTLX where XMDM = a.YLTLX) YLTLXMC,");
		sbuf.append("(select XMMC from TB_ZY_JGQY_QYZT where XMDM = a.QYZT) QYZTMC,");
		sbuf.append("(select XMMC from TB_ZY_YXQ where XMDM = b.QYYXQ) QYYXQMC \n");
		sbuf.append(" from "+this.tb.getTableName()+" a,"+this.glTb.getTableName()+" b,TB_ZD_YLJGXX c  where a.ZDYLJGID=c.PTJGID and a.YLTID = b.YLTID and b.JGLX = '3'  and b.JLZT = '1'  ");
		List list = new ArrayList();
		String value = map.get("YLTID");
		if(!StringUtils.isEmpty(value))
		{
			sbuf.append(" and a.YLTID = ? ");
			list.add(value);
		}
		value = map.get("YLTMC");
		if(!StringUtils.isEmpty(value))
		{
			sbuf.append(" and a.YLTMC like ? ");
			list.add("%"+value+"%");
		}
		value = map.get("YLTLX");
		if(!StringUtils.isEmpty(value))
		{
			sbuf.append(" and a.YLTLX = ? ");
			list.add(value);
		}
		value = map.get("ZDYLJGMC");
		if(!StringUtils.isEmpty(value))
		{
			sbuf.append(" and c.YLJGMC like ? ");
			list.add("%"+value+"%");
		}
		value = map.get("YLTZT");
		if(!StringUtils.isEmpty(value))
		{
			sbuf.append(" and a.YLTZT = ? ");
			list.add(value);
		}
		value = map.get("YLJGID");
		if(!StringUtils.isEmpty(value))
		{
			sbuf.append(" and a.YLTID not in (select distinct YLTID from TB_JGQY_YLT_YLJG where YLJGID = ? and QYZT in (01,02,03))");
			list.add(value);
		}
		//sbuf.append(" where a.YLTID = ? ");
		/*SQLBean sb = new SQLBean(sbuf.toString(),new String[]{map.get("YLTID")});
		return super.queryOne(sb);*/
		PageQuerySQLBean sb = super.checkAndCreatePageQuerySQLBean(map, sbuf.toString(), " order by a.YLTID,c.QYSJ ", list.toArray());
		return super.queryPage(sb, ResultHandlerConstant.JSONARRAY);
	}

	/**
	 * 根据关联ID查询记录
	 * @param yltidAry
	 * @return
	 */
	/*public JSONArray queryList(String[] glidAry) {
		StringBuffer sbuf = new StringBuffer("select a.*,b.YLTMC,b.YLTLX,b.BZSM,b.CJSJ,b.ZDYLJGID,b.YLTZT,b.SFXY, ");
		sbuf.append("(select YLJGMC from TB_ZD_YLJGXX where PTJGID = a.YLJGID) YLJGMC,");
		sbuf.append("(select YLJGMC from TB_ZD_YLJGXX where PTJGID = b.ZDYLJGID) ZDYLJGMC,");
		sbuf.append("(select XMMC from TB_ZY_JGQY_QYZT where XMDM = a.QYZT) QYZTMC,");
		sbuf.append("(select XMMC from TB_ZY_YXQ where XMDM = a.QYYXQ) QYYXQMC,");
		sbuf.append("(select XMMC from TB_ZY_YLTLX where XMDM = b.YLTLX) YLTLXMC \n");
		sbuf.append(" from "+this.glTb.getTableName()+" a, "+this.tb.getTableName()+" b ");
		sbuf.append(" where a.YLTID = b.YLTID  and a.GLID in (");
		String params = "";
		for (String string : glidAry) {
			params += "?,";
		}
		params = params.substring(0, params.lastIndexOf(","));
		sbuf.append(params);
		sbuf.append(") \n");
		SQLBean sb = new SQLBean(sbuf.toString(), glidAry);
		return super.queryList(sb);
	}*/

	@Override
	public boolean isExit(Map<String, String> map) {
		StringBuffer sbuf = new StringBuffer("select count(glid) total from "+this.glTb.getTableName());
		sbuf.append(" where YLTID = ?  and JLZT = '1'  and QYZT in (03,07)");
		SQLBean sb = new SQLBean(sbuf.toString(),new String[]{map.get("YLTID")});
		return super.isExist(sb);
	}
	
	public JSONObject queryPageLs(Map<String, String> map) {
		StringBuffer sbuf = new StringBuffer("select a.*,c.YLJGMC ZDYLJGMC, ");
		sbuf.append("b.YLTMC,b.YLTLX,b.BZSM,b.CJSJ,b.ZDYLJGID,b.YLTZT,b.SFXY,");
		sbuf.append("(select YLJGMC from TB_ZD_YLJGXX where PTJGID = a.YLJGID) YLJGMC,");
		sbuf.append("(select XMMC from TB_ZY_YXQ where XMDM = a.QYYXQ) QYYXQMC,");
		sbuf.append("(select XMMC from TB_ZY_JYYY where XMDM = a.JYYY) JYYYMC,");
		sbuf.append("(select XZQHMC from VIEW_YLJGXX where PTJGID = a.YLJGID) XZQHMC,");
		sbuf.append("(select WSJGLBMC from VIEW_YLJGXX where PTJGID = a.YLJGID) WSJGLBMC,");
		sbuf.append("(select YYDJMC from VIEW_YLJGXX where PTJGID = a.YLJGID) YYDJMC,");
		sbuf.append("(select JGFLGLMC from VIEW_YLJGXX where PTJGID = a.YLJGID) JGFLGLMC,");
		sbuf.append("(select YLJGMC from TB_ZD_YLJGXX where PTJGID = a.JYFQJGID) JYFQJGMC, \n");
		sbuf.append("(select XMMC from TB_ZY_JGQY_QYZT where XMDM = a.QYZT) QYZTMC,");
		sbuf.append("(select XMMC from TB_ZY_YLTLX where XMDM = b.YLTLX) YLTLXMC \n");
		sbuf.append(" from "+this.glTb.getTableName()+" a, "+this.tb.getTableName()+" b,TB_ZD_YLJGXX c ");
		sbuf.append(" where a.YLTID = b.YLTID and b.ZDYLJGID = c.PTJGID and b.ZDYLJGID <> a.YLJGID ");
		List list = new ArrayList();
		String value = map.get("JLZT");
		if(!StringUtils.isEmpty(value))
		{
			sbuf.append("and a.JLZT = ? ");
			list.add(value);
		}
		value = map.get("YLTID");
		if(!StringUtils.isEmpty(value))
		{
			sbuf.append("and a.YLTID = ? ");
			list.add(value);
		}
		PageQuerySQLBean sb = super.checkAndCreatePageQuerySQLBean(map, sbuf.toString(), "order by a.QYSJ DESC", list.toArray());
		return super.queryPage(sb, ResultHandlerConstant.JSONARRAY);
	}

}
